# -*- coding: utf-8 -*-
# File : analysis_service.py
# Author: taoyahui
# Date : 2021/10/19
from config import db
from sqlalchemy import text
from utils import datetime_utils
from datetime import timedelta, datetime
import results


def line_order_service():
    order_line_keys, order_line_values = line_service_impl('FoodTraceNew_create_order_method')
    produce_line_keys, produce_order_line_values = line_service_impl('FoodTraceNew_produces_method')
    buy_line_keys, buy_order_line_values = line_service_impl('FoodTraceNew_buy_method')
    ship_line_keys, ship_order_line_values = line_service_impl('FoodTraceNew_transport_method')
    return results.gen_result(200, {
        'time': order_line_keys,
        'order': order_line_values,
        'produce':produce_order_line_values,
        'buy': buy_order_line_values,
        'ship': ship_order_line_values
    })


def line_service_impl(table_name):
    sql = text(f"""SELECT DATE_FORMAT(block_time_stamp, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS num
FROM {table_name} GROUP BY time ORDER BY time """)
    res = db.engine.execute(sql)
    res_list = [row for row in res]
    start_time = datetime.strptime(res_list[0][0], '%Y-%m-%d %H:%M:%S')
    end_time = datetime.strptime(res_list[-1][0], '%Y-%m-%d %H:%M:%S')
    time_dict = {}
    for result in datetime_utils.time_delta_gen(start_time, end_time, timedelta(hours=1)):
        time_dict[datetime.strftime(result, '%Y-%m-%d %H:%M:%S')] = 0
    for get_res in res_list:
        time_dict[get_res[0]] = get_res[1]
    return list(time_dict.keys()), list(time_dict.values())


def column_service():
    sql = text(f"""select a.num , b.name from (
select food_id , count(1) as num from FoodTraceNew_create_order_method GROUP BY food_id ORDER BY food_id
)as a left join FoodTraceNew_create_food_method as b on a.food_id = b.food_id""")
    res = db.engine.execute(sql)
    res_list = [row for row in res]
    name_list = [row[1] for row in res_list]
    num_list = [row[0] for row in res_list]
    return results.gen_result(200, {
        'name':name_list,
        'num_list':num_list
    })


def food_pie_service():
    sql = text(f"""select a.num , b.name from (
    select food_id , count(1) as num from FoodTraceNew_create_order_method GROUP BY food_id ORDER BY food_id
    )as a left join FoodTraceNew_create_food_method as b on a.food_id = b.food_id""")
    res = db.engine.execute(sql)
    res_list = [row for row in res]
    ret_list = []
    for row in res_list:
        ret_list.append({
            'name':row[1],
            'value':row[0]
        })
    return results.gen_result(200, ret_list)